package com.example.demo.controller;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MysqlToContent {
    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://localhost:3306/sky_take_out";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "123456";
    // 批处理大小
    private static final int BATCH_SIZE = 1000;

    public static void main(String[] args) {
        // 指定要处理的表名
        String tableName = "category";
        // 指定输出文件路径
        String outputPath = "E:\\workspace\\LangChain4J-demo\\src\\main\\resources\\content\\category_docs.jsonl";

        try {
            convertTableToDocuments(tableName, outputPath);
            System.out.println("数据转换完成，文档已保存至: " + outputPath);
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将指定表的数据转换为文档并保存到文件
     */
    public static void convertTableToDocuments(String tableName, String outputPath) throws SQLException, IOException {
        String sql = buildSelectQuery(tableName);

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql);
             BufferedWriter writer = new BufferedWriter(new FileWriter(outputPath))) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 获取列名列表（排除不想要的列）
            List<String> columnNames = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                // 可在此处过滤不需要的列（如自动递增的ID、时间戳等）
                if (!columnName.equals("id") && !columnName.equals("create_time")
                        && !columnName.equals("update_time")&& !columnName.equals("create_user")
                        && !columnName.equals("update_user")&& !columnName.equals("image")
                        && !columnName.equals("id_number") && !columnName.equals("password")) {
                    columnNames.add(columnName);
                }
            }

            // 逐行处理结果集
            while (rs.next()) {
                // 构建文档对象
                Map<String, Object> document = new HashMap<>();
                // 添加主键ID（假设表中有id列作为唯一标识）
                document.put("id", rs.getInt("id"));

                // 构建文本描述
                StringBuilder textBuilder = new StringBuilder();
                textBuilder.append(tableName).append(": ");

                for (String columnName : columnNames) {
                    Object value = rs.getObject(columnName);
                    if (value != null) {
                        // 清理特殊字符，避免JSON格式错误
                        String cleanValue = sanitizeValue(value.toString());
                        textBuilder.append(columnName).append(": ").append(cleanValue).append("; ");
                    }
                }

                document.put("text", textBuilder.toString());

                // 写入JSONL文件
                writer.write(toJsonString(document));
                writer.newLine();
            }
        }
    }

    /**
     * 构建SELECT查询语句
     */
    private static String buildSelectQuery(String tableName) {
        return "SELECT * FROM " + tableName;
    }

    /**
     * 清理值中的特殊字符
     */
    private static String sanitizeValue(String value) {
        // 移除换行符和制表符
        value = value.replace("\n", " ").replace("\r", " ").replace("\t", " ");
        // 转义双引号
        value = value.replace("\"", "\\\"");
        return value;
    }

    /**
     * 将Map转换为JSON字符串（简化实现，不依赖外部库）
     */
    private static String toJsonString(Map<String, Object> map) {
        StringBuilder sb = new StringBuilder();
        sb.append("{");

        boolean first = true;
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            if (!first) {
                sb.append(", ");
            }

            sb.append("\"").append(entry.getKey()).append("\": ");

            Object value = entry.getValue();
            if (value instanceof String) {
                sb.append("\"").append(value).append("\"");
            } else {
                sb.append(value);
            }

            first = false;
        }

        sb.append("}");
        return sb.toString();
    }
}